using Common;
using Microsoft.Data.Sqlite;

namespace GrpcService.Common
{
    public class DatabaseUploadSessionManager : IUploadSessionManager
    {
        private readonly string _connectionString;
        private readonly string _tempStoragePath;

        public DatabaseUploadSessionManager(string dbPath, string tempStoragePath)
        {
            _connectionString = $"Data Source={dbPath}";
            _tempStoragePath = tempStoragePath;
            Directory.CreateDirectory(_tempStoragePath);
            InitializeDatabase();
        }

        private void InitializeDatabase()
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                CREATE TABLE IF NOT EXISTS UploadSessions (
                    SessionId TEXT PRIMARY KEY,
                    FileName TEXT NOT NULL,
                    FileSize INTEGER NOT NULL,
                    FileHash TEXT NOT NULL,
                    UploadedBytes INTEGER NOT NULL,
                    TempFilePath TEXT NOT NULL,
                    CreatedAt TEXT NOT NULL,
                    CompletedAt TEXT
                )";
            command.ExecuteNonQuery();
        }

        public string CreateSession(string fileName, long fileSize, string fileHash)
        {
            var sessionId = Guid.NewGuid().ToString();
            var tempFilePath = Path.Combine(_tempStoragePath, $"temp_{sessionId}_{Path.GetFileName(fileName)}");

            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                INSERT INTO UploadSessions 
                (SessionId, FileName, FileSize, FileHash, UploadedBytes, TempFilePath, CreatedAt)
                VALUES 
                (@SessionId, @FileName, @FileSize, @FileHash, 0, @TempFilePath, @CreatedAt)";

            command.Parameters.AddWithValue("@SessionId", sessionId);
            command.Parameters.AddWithValue("@FileName", fileName);
            command.Parameters.AddWithValue("@FileSize", fileSize);
            command.Parameters.AddWithValue("@FileHash", fileHash);
            command.Parameters.AddWithValue("@TempFilePath", tempFilePath);
            command.Parameters.AddWithValue("@CreatedAt", DateTime.UtcNow.ToString("o"));

            command.ExecuteNonQuery();

            return sessionId;
        }

        public UploadSession GetSession(string sessionId)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM UploadSessions WHERE SessionId = @SessionId";
            command.Parameters.AddWithValue("@SessionId", sessionId);

            using var reader = command.ExecuteReader();
            if (reader.Read())
            {
                return new UploadSession
                {
                    SessionId = reader.GetString(0),
                    FileName = reader.GetString(1),
                    FileSize = reader.GetInt64(2),
                    FileHash = reader.GetString(3),
                    UploadedBytes = reader.GetInt64(4),
                    TempFilePath = reader.GetString(5),
                    CreatedAt = DateTime.Parse(reader.GetString(6)),
                    CompletedAt = reader.IsDBNull(7) ? null : DateTime.Parse(reader.GetString(7))
                };
            }

            return null;
        }

        public UploadSession FindSession(string fileName, string fileHash)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                SELECT * FROM UploadSessions 
                WHERE FileName = @FileName AND FileHash = @FileHash
                ORDER BY CreatedAt DESC
                LIMIT 1";

            command.Parameters.AddWithValue("@FileName", fileName);
            command.Parameters.AddWithValue("@FileHash", fileHash);

            using var reader = command.ExecuteReader();
            if (reader.Read())
            {
                return new UploadSession
                {
                    SessionId = reader.GetString(0),
                    FileName = reader.GetString(1),
                    FileSize = reader.GetInt64(2),
                    FileHash = reader.GetString(3),
                    UploadedBytes = reader.GetInt64(4),
                    TempFilePath = reader.GetString(5),
                    CreatedAt = DateTime.Parse(reader.GetString(6)),
                    CompletedAt = reader.IsDBNull(7) ? null : DateTime.Parse(reader.GetString(7))
                };
            }

            return null;
        }

        public void UpdateSessionProgress(string sessionId, long uploadedBytes)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                UPDATE UploadSessions 
                SET UploadedBytes = @UploadedBytes 
                WHERE SessionId = @SessionId";

            command.Parameters.AddWithValue("@SessionId", sessionId);
            command.Parameters.AddWithValue("@UploadedBytes", uploadedBytes);

            command.ExecuteNonQuery();
        }

        public long GetUploadedBytes(string sessionId)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = "SELECT UploadedBytes FROM UploadSessions WHERE SessionId = @SessionId";
            command.Parameters.AddWithValue("@SessionId", sessionId);

            var result = command.ExecuteScalar();
            return result != null ? Convert.ToInt64(result) : 0;
        }

        public void CompleteSession(string sessionId)
        {
            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = @"
                UPDATE UploadSessions 
                SET CompletedAt = @CompletedAt 
                WHERE SessionId = @SessionId";

            command.Parameters.AddWithValue("@SessionId", sessionId);
            command.Parameters.AddWithValue("@CompletedAt", DateTime.UtcNow.ToString("o"));

            command.ExecuteNonQuery();
        }

        public void AbortSession(string sessionId)
        {
            var session = GetSession(sessionId);
            if (session != null)
            {
                try
                {
                    if (File.Exists(session.TempFilePath))
                    {
                        File.Delete(session.TempFilePath);
                    }
                }
                catch
                {
                    // Log error if needed
                }

                using var connection = new SqliteConnection(_connectionString);
                connection.Open();

                var command = connection.CreateCommand();
                command.CommandText = "DELETE FROM UploadSessions WHERE SessionId = @SessionId";
                command.Parameters.AddWithValue("@SessionId", sessionId);
                command.ExecuteNonQuery();
            }
        }

        public void CleanupExpiredSessions(TimeSpan expirationTime)
        {
            var cutoff = DateTime.UtcNow - expirationTime;

            using var connection = new SqliteConnection(_connectionString);
            connection.Open();

            // First get all expired sessions
            var selectCommand = connection.CreateCommand();
            selectCommand.CommandText = @"
                SELECT SessionId, TempFilePath FROM UploadSessions 
                WHERE CreatedAt < @Cutoff AND (CompletedAt IS NULL OR CompletedAt < @Cutoff)";
            selectCommand.Parameters.AddWithValue("@Cutoff", cutoff.ToString("o"));

            var sessionsToDelete = new List<(string SessionId, string TempFilePath)>();

            using (var reader = selectCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    sessionsToDelete.Add((reader.GetString(0), reader.GetString(1)));
                }
            }

            // Then delete them and their temp files
            foreach (var (sessionId, tempFilePath) in sessionsToDelete)
            {
                try
                {
                    if (File.Exists(tempFilePath))
                    {
                        File.Delete(tempFilePath);
                    }
                }
                catch
                {
                    // Log error if needed
                }

                var deleteCommand = connection.CreateCommand();
                deleteCommand.CommandText = "DELETE FROM UploadSessions WHERE SessionId = @SessionId";
                deleteCommand.Parameters.AddWithValue("@SessionId", sessionId);
                deleteCommand.ExecuteNonQuery();
            }
        }
    }
}